﻿package com.vsoft.anti_fra;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.vsoft.framework.core.BaseException;
import com.vsoft.framework.core.BusiObj;
import com.vsoft.framework.core.IService;
import com.vsoft.framework.core.db.OperAgent;
import com.vsoft.framework.core.db.SQLArgs;
import com.vsoft.framework.core.util.BOHelper;
import com.vsoft.framework.core.util.LogCollect;

public class RuleConf implements IService {
	private static Logger	vLogger		= Logger.getLogger(RuleConf.class);

	private String				vName			= RuleConf.class.getName();

	private Connection		vConn			= null;

	private SQLArgs				vArg			= new SQLArgs();

	private BusiObj				vInBO			= null;

	private StringBuffer	vStrBuf		= new StringBuffer();

	private String				staffNo		= "";

	private String				staffCode	= "";

	private String				staffName	= "";

	private String				gSql			= "";
	//////////////////////////////////////add at 1104
	private int pageIndex=1,pageCount=1,pageRows=30;
	private int startRowNum,endRowNum,totalRows;
	boolean flagQueryCount=false;

	public BusiObj execute(BusiObj obj) throws BaseException {
		vInBO = obj;
		vLogger.info("=================="+vName+"excute" );
		vConn = vInBO.getConnection();
		
		String s = vInBO.getName();
		vLogger.info("=================="+vName+"'s bo is " + BOHelper.boToXML(vInBO));
		
		//BusiObj session = vInBO.getBusiObj("SESSION_DATA");
		staffNo = vInBO.getString("SESSION_STAFF_NO", "");
		staffCode = vInBO.getString("SESSION_STAFF_CODE", "");
		staffName = vInBO.getString("SESSION_STAFF_NAME", "");
		////////////////////////////////////////add at 1104
		String pageRow=vInBO.getString("pageRows","");
		if(!pageRow.trim().equals(""))
			this.setPageRows(Integer.parseInt(pageRow));
		String total =vInBO.getString("totalRows","");
		if(!total.trim().equals(""))
		{
			totalRows=Integer.parseInt(total);
			if (totalRows<=0)
				flagQueryCount=true;
		}
		else
			flagQueryCount=true;
			
		String curPageNo=vInBO.getString("curPageNo","");
		if(!curPageNo.trim().equals(""))
			pageIndex =Integer.parseInt(curPageNo);
		else
			pageIndex =1;
		this.setPageRowNum(pageIndex);
		////////////////////////////////////////add at 1104
		if (s.equals("AF_GetBlackList"))
			return GetBlackList();
		else if (s.equals("AF_SetBlackList"))
			return SetBlackList();
		if (s.equals("AF_GetWhiteList"))
			return GetWhiteList();
		else if (s.equals("AF_SetWhiteList"))
			return SetWhiteList();
		if (s.equals("AF_GetExpList"))
			return GetExpList();
		else if (s.equals("AF_SetExpList"))
			return SetExpList();
		if (s.equals("AF_GetSmsList"))
			return GetSmsList();
		else if (s.equals("AF_SetSmsList"))
			return SetSmsList();
		if (s.equals("AF_GetIvrList"))
			return GetIvrList();
		if (s.equals("AF_GetIvrCountByArea"))
			return GetIvrCountByArea();
		if (s.equals("AF_GetIvrCountByAreaCallnum"))
			return GetIvrCountByAreaCallnum();
		if (s.equals("AF_GetExpDetList"))
			return GetExpDetList();
		else
			throw new BaseException(vName + "-001", vInBO.getName() + "没有匹配服务");
	}
	
	public BusiObj GetBlackList() throws BaseException {
		vArg.reset();
		String searchText = ((String) vInBO.getValue("search_text", "")).trim();
		gSql = "select a.num id, a.num,a.state,a.state_time,nvl(b.name,' ')oper_staff_name,nvl(ivr_flag,'0') ivr_flag "
				+ "from black_list_card a,staff b " + " where a.oper_staff_code = b.staff_code(+) " + " and b.state(+) = '1'";
		if (searchText != null && searchText.length() > 0) {
			gSql = gSql + " and a.num = :search_text ";
			vArg.setArg("search_text", searchText);
		}
		BusiObj rsBo = OperAgent.execQuery(vConn, "GetBlackList", gSql, vArg);
		vLogger.info("返回数据是:\n\n\n\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}

	public BusiObj SetBlackList() throws BaseException {
		//String strType = vInBO.getString("NUM");
		ArrayList<BusiObj> member = vInBO.getBusiObjList("BLACK_LIST");
		String newFlag = vInBO.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		for (BusiObj m : member) {

			String id = (String) m.getValue("id", true);

			OperAgent oa = new OperAgent(vConn);
			oa.addSQL("delete black_list_card where num=:num");
			oa.prepare();
			oa.bind("num", id);
			oa.run();
			oa.reset();
			if (newFlag.equals("1")) {
				String num = m.getString("num");
				String state = m.getString("state");
				String ivr_flag = m.getString("ivr_flag");
				oa.addSQL("insert into black_list_card (num,state,state_time,oper_staff_code,ivr_flag) "
						+ " values (:num,:state,sysdate,:oper_staff_code,:ivr_flag)");
				oa.prepare();
				oa.bind("num", num);
				oa.bind("state", state);

				oa.bind("oper_staff_code", this.staffCode);
				oa.bind("ivr_flag", ivr_flag);
				oa.run();
				oa.reset();
			}
		}
		vInBO.setValue("ResultFlag", "0");
		return vInBO;
	}

	public BusiObj GetWhiteList() throws BaseException {
		vArg.reset();
		String searchText = ((String) vInBO.getValue("search_text", "")).trim();
		gSql = "select a.num id, a.num,a.state,a.state_time,nvl(b.name,' ') oper_staff_name " + "from white_list_card a,staff b "
				+ " where a.oper_staff_code = b.staff_code(+) " + " and b.state(+) = '1'";
		if (searchText != null && searchText.length() > 0) {
			gSql = gSql + " and a.num = :search_text ";
			vArg.setArg("search_text", searchText);
		}
		BusiObj rsBo = OperAgent.execQuery(vConn, "GetWhiteList", gSql, vArg);
		vLogger.info("返回数据是:\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}

	public BusiObj SetWhiteList() throws BaseException {
		//String strType = vInBO.getString("NUM");
		ArrayList<BusiObj> member = vInBO.getBusiObjList("WHITE_LIST");
		String newFlag = vInBO.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		for (BusiObj m : member) {

			String id = (String) m.getValue("id", true);

			OperAgent oa = new OperAgent(vConn);
			oa.addSQL("delete white_list_card where num=:num");
			oa.prepare();
			oa.bind("num", id);
			oa.run();
			oa.reset();
			if (newFlag.equals("1")) {
				String num = m.getString("num");
				String state = m.getString("state");
				oa.addSQL("insert into white_list_card (num,state,state_time,oper_staff_code) values (:num,:state,sysdate,:oper_staff_code)");
				oa.prepare();
				oa.bind("num", num);
				oa.bind("state", state);
				oa.bind("oper_staff_code", this.staffCode);
				oa.run();
				oa.reset();
			}
		}
		vInBO.setValue("ResultFlag", "0");
		return vInBO;
	}

	public BusiObj GetExpList() throws BaseException {
		vArg.reset();
		String searchText = ((String) vInBO.getValue("search_text", "")).trim();
		gSql = "select a.num id, a.num,a.state,a.state_time,a.duration,a.times,nvl(b.name,' ') oper_staff_name " + "from Exception_num_list a,staff b "
				+ " where a.oper_staff_code = b.staff_code(+) " + " and b.state(+) = '1'";
		if (searchText != null && searchText.length() > 0) {
			gSql = gSql + " and a.num = :search_text ";
			vArg.setArg("search_text", searchText);
		}
		BusiObj rsBo = OperAgent.execQuery(vConn, "GetExpList", gSql, vArg);
		vLogger.info("返回数据是:\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}

	public BusiObj SetExpList() throws BaseException {
		ArrayList<BusiObj> member = vInBO.getBusiObjList("EXP_LIST");
		String newFlag = vInBO.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		for (BusiObj m : member) {

			String id = (String) m.getValue("id", true);

			OperAgent oa = new OperAgent(vConn);
			oa.addSQL("delete Exception_num_list where num=:num");
			oa.prepare();
			oa.bind("num", id);
			oa.run();
			oa.reset();
			if (newFlag.equals("1")) {
				String num = m.getString("num");
				String state = m.getString("state");
				String duration = m.getString("duration");
				String times = m.getString("times");
				oa.addSQL("insert into Exception_num_list (num,state,state_time,oper_staff_code,duration,times) values (:num,:state,sysdate,:oper_staff_code,:duration,:times)");
				oa.prepare();
				oa.bind("num", num);
				oa.bind("state", state);
				oa.bind("oper_staff_code", this.staffCode);
				oa.bind("duration",duration);
				oa.bind("times",times);
				oa.run();
				oa.reset();
			}
		}
		vInBO.setValue("ResultFlag", "0");
		return vInBO;
	}

	public BusiObj GetSmsList() throws BaseException {
		vArg.reset();
		String searchText = ((String) vInBO.getValue("search_text", "")).trim();
		gSql = "select a.num id, a.num, a.sms_content, a.state,a.state_time,nvl(b.name,' ') oper_staff_name "
				+ "from release_sms_list a,staff b " + " where a.oper_staff_code = b.staff_code(+) " + " and b.state(+) = '1'";
		if (searchText != null && searchText.length() > 0) {
			gSql = gSql + " and a.num = :search_text ";
			vArg.setArg("search_text", searchText);
		}

		BusiObj rsBo = OperAgent.execQuery(vConn, "GetSmsList", gSql, vArg);
		vLogger.info("返回数据是:\n\n\n\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}

	public BusiObj SetSmsList() throws BaseException {
		//String strType = vInBO.getString("NUM");
		ArrayList<BusiObj> member = vInBO.getBusiObjList("SMS_LIST");
		String newFlag = vInBO.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		for (BusiObj m : member) {

			String id = (String) m.getValue("id", true);

			OperAgent oa = new OperAgent(vConn);
			oa.addSQL("delete release_sms_list where num=:num");
			oa.prepare();
			oa.bind("num", id);
			oa.run();
			oa.reset();
			if (newFlag.equals("1")) {
				String num = m.getString("num");
				String state = m.getString("state");
				String smsContent = m.getString("sms_content");
				oa.addSQL("insert into release_sms_list (num,sms_content,state,state_time,oper_staff_code) values (:num,:sms_content,:state,sysdate,:oper_staff_code)");
				oa.prepare();
				oa.bind("num", num);
				oa.bind("sms_content", smsContent);
				oa.bind("state", state);
				oa.bind("oper_staff_code", this.staffCode);
				oa.run();
				oa.reset();
			}
		}
		vInBO.setValue("ResultFlag", "0");
		return vInBO;
	}
	//////////////////////////////////////////////////////////add at 1104

	public void setPageRows(int rows)
	{
		pageRows	=	rows;
	}
	private String getCountSql(String sql)throws BaseException 
	{
		return "select count(1) totalRows,ceil(count(1)/:pageRows) pageCount from ("+sql+" )";
	}

	public BusiObj getPagingCount(String srcSql,SQLArgs sqlArgs,String queryName)throws BaseException
	{
		String Sql	=	this.getCountSql(srcSql);
		sqlArgs.setArg("pageRows", pageRows);
		this.vLogger.info("getPagingCount:------"+Sql);
		return OperAgent.execQuery(vConn, queryName, Sql, sqlArgs);
	}
	
	private String getPagingSql(String srcSql)throws BaseException 
	{
		return "select * from ( " +
				" select aaa.*,rownum rn from " +
				" ("+srcSql+") aaa " +
				"  where rownum <= :endRowNum " +
				"  ) bbb " +
				" where rn >:startRowNum";
	}
	public BusiObj getPagingInfo(String srcSql,SQLArgs sqlArgs,String queryName)throws BaseException
	{
		String Sql	=	this.getPagingSql(srcSql);
		sqlArgs.setArg("endRowNum", endRowNum);
		sqlArgs.setArg("startRowNum", startRowNum);
		return OperAgent.execQuery(vConn, queryName, Sql, sqlArgs);
	}
	private void setPageRowNum(int curPageNo)throws BaseException
	{
		if(curPageNo<1) curPageNo	=	1;
		startRowNum	=	(curPageNo-1)*pageRows;
		endRowNum	=	(curPageNo)*pageRows;
		//return OperAgent.execQuery(vConn, queryName, gSql, vArg);
	}

	//////////////////////////////////////////////////////////add at 1104
	
	
	public BusiObj GetIvrList() throws BaseException 
	{
		vLogger.info("================start GetIvrList:" );
		BusiObj rsBo=null;
		try {
			vArg.reset();
			String searchType = ((String) vInBO.getValue("search_type", "")).trim();
			String searchText = ((String) vInBO.getValue("search_text", "")).trim();
			String only110_flag = ((String) vInBO.getValue("only110_flag", "")).trim();
			String expAllFlag= ((String) vInBO.getValue("expAllFlag", "")).trim();//add at 20101111
			boolean search_flag = false;
			gSql = "select CALLING_NUM,decode(CALLING_ATTR,5,'逻辑号码', 6,'物理号码',7,'TID',CALLING_ATTR) CALLING_ATTR,CALLED_NUM,"
					+ "decode(CALLED_ATTR,5,'逻辑号码', 6,'物理号码',7,'TID',CALLED_ATTR) CALLED_ATTR,CALLING_AREA,CALLED_AREA,CALLING_PHY_NUM,"
					+ "SIGNALING_TYPE,CDR_TYPE,CALLING_PRESENT,RELEASE_TYPE,RELEASE_TIME,CALLED_PHY_NUM,CALLTIME,DURATION,OPC,DPC,MAC_ADDR,IP_ADDR,"
					+ "IVRCALL_SDATE,decode(STATUS,0,'呼出成功',1,'任务过期',2,'正在呼出',3,'等待呼出','4','任务失败',STATUS) STATUS,"
					+ "IVRCALL_EDATE,IVRCALL_OPDATE,REMARK,TASKID from ivr_call_record " 
					+ " where 1=1 " //add at 1106
					+" and not(called_area is  null and trim(called_area) ='' ) " //add at 20101111
					//+ " and status =0"
					;
			if (searchText != null && searchText.length() > 0) {
				if (searchType.equals("0")) {
					gSql = gSql + " and  CALLING_NUM like '%"+ searchText+"%' ";
					//vArg.setArg("search_text", searchText);
					search_flag = true;
				} else if (searchType.equals("1")) {
					gSql = gSql + " and CALLED_NUM like'%"+ searchText+"%' ";
					//vArg.setArg("search_text", searchText);
					search_flag = true;
				}
				else if (searchType.equals("2")) {
					gSql = gSql + " and CALLED_AREA like'%"+ searchText+"%' ";
					//vArg.setArg("search_text", searchText);
					search_flag = true;
				}
			}
			if (only110_flag.trim().equals("1")) {
				String where = "";
/*				if (!search_flag)
					where = " where ";
				else
					where = " and ";*/
				gSql = gSql + where + " and status in(0,4) ";//status<5 //modified at 1106
			}
			vLogger.info("================ GetIvrList:gSql="+gSql );
			if(!expAllFlag.equals("1"))
			{
				////////////////////////////////////////add at 1104
				if(this.flagQueryCount)
				{
					rsBo = this.getPagingCount(gSql, vArg, "GetIvrList");
	/*				this.totalRows=countBo.getInt("totalRows");
					this.pageCount=countBo.getInt("pageCount");
					rsBo.setString("pageCount", String.valueOf(pageCount));
					rsBo.setString("totalRows", String.valueOf(totalRows));*/
				}
				else
				{
					gSql+=" order by CALLTIME desc,status asc  ";
					rsBo = this.getPagingInfo(gSql, vArg, "GetIvrList");
				}
				////////////////////////////////////////add at 1104
			}
			else
			{
				rsBo=OperAgent.execQuery(vConn,  "GetIvrList", gSql, vArg);
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new BaseException(vName + "-013", "获取外呼详单失败");
		}
			//OperAgent.execQuery(vConn, "GetIvrList", gSql, vArg);
		rsBo.setValue("ResultFlag", "0");
		//vLogger.info("返回数据是:\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}

	public BusiObj SetIvrList() throws BaseException {
		ArrayList<BusiObj> member = vInBO.getBusiObjList("EXP_LIST");
		String newFlag = vInBO.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		for (BusiObj m : member) {

			String id = (String) m.getValue("id", true);

			OperAgent oa = new OperAgent(vConn);
			oa.addSQL("delete Exception_num_list where num=:num");
			oa.prepare();
			oa.bind("num", id);
			oa.run();
			oa.reset();
			if (newFlag.equals("1")) {
				String num = m.getString("num");
				String state = m.getString("state");
				oa.addSQL("insert into Exception_num_list (num,state,state_time,oper_staff_code) values (:num,:state,sysdate,:oper_staff_code)");
				oa.prepare();
				oa.bind("num", num);
				oa.bind("state", state);
				oa.bind("oper_staff_code", this.staffCode);
				oa.run();
				oa.reset();
			}
		}
		vInBO.setValue("ResultFlag", "0");
		return vInBO;
	}

	public String formatTime(String time, int format_type) throws BaseException {
		String return_value = "";
		int length = time.trim().length();
		if (length == 8)
			return_value = time + " 00:00:01";
		else if (length == 0)
			if (format_type == 1)//开始时间
				return_value = "2000-01-01 00:00:01";
			else
				return_value = "2099-01-01 00:00:01";//结束时间
		else
			return_value = time;
		return return_value;

	}

	public BusiObj GetIvrCountByArea() throws BaseException {
		BusiObj rsBo = null;
		try {
			vArg.reset();
			String start_time = this.formatTime(((String) vInBO.getValue("start_time", "")).trim(), 1);
			String end_time = this.formatTime(((String) vInBO.getValue("end_time", "")).trim(), 2);
			String cheatCountFlag	=	((String) vInBO.getValue("cheatCountFlag", "")).trim();//add at 20101111
			String condCheatCount = " and status in(0,4) ";
			if(cheatCountFlag.equals("1"))
				condCheatCount="";

/*			gSql = " select count(1) IVR_COUNT,CALLED_AREA from " + " (select nvl(called_area,'无区号')called_area from ivr_call_record a "
					+ " where a.CALLtime between to_date(:start_time,'yyyy-mm-dd HH24:MI:SS') "
					+ " and to_date(:end_time,'yyyy-mm-dd HH24:MI:SS') and called_area>0) group by called_area";
			gSql = gSql + " union select count(1) IVR_COUNT,'小计' CALLED_AREA from ivr_call_record "
					+ " where CALLtime between to_date(:start_time,'yyyy-mm-dd HH24:MI:SS') "
					+ " and to_date(:end_time,'yyyy-mm-dd HH24:MI:SS' )and called_area>0";*/
			
			gSql = 	
					" select sum(valid_IVR_COUNT) valid_IVR_COUNT,sum(invalid_IVR_COUNT) invalid_IVR_COUNT," +
					" sum(invalid_IVR_COUNT)+sum(valid_IVR_COUNT) IVR_COUNT," +
					" Decode(Grouping(CALLED_AREA),1,'小计',CALLED_AREA) CALLED_AREA  " +
					" from ( " +
					" select decode(status,0,IVR_COUNT,0) valid_IVR_COUNT,decode(status,1,IVR_COUNT,0) invalid_IVR_COUNT,CALLED_AREA from " +
					" (select count(1) IVR_COUNT, CALLED_AREA,status " +
					" from (select  called_area,decode(status,0,0,2,0,3,0,1) status " +
					" from ivr_call_record a " +
					" where a.CALLtime between to_date(:start_time,'yyyy-mm-dd HH24:MI:SS') " +
					" and to_date(:end_time,'yyyy-mm-dd HH24:MI:SS') " +
					" and called_area>0 " +
					condCheatCount +//status <8 modify at 20101106
					"  ) group by called_area, status " +
					"  )" 
					+
					"  )" +
					"  group by " +
					//" rollup(" +
					" called_area" 
					//+" ) "
					;
			
			vArg.setArg("start_time", start_time);
			vArg.setArg("end_time", end_time);
			vLogger.info("sql----------:" + gSql);
			rsBo = OperAgent.execQuery(vConn, "GetIvrCountByArea", gSql, vArg);
		} catch (Exception e) {
			e.printStackTrace();
			throw new BaseException(vName + "-010", "按照区号外呼统计失败");
		}
		vLogger.info("返回数据是:\n\n\n\n" + BOHelper.boToXML(rsBo));
		rsBo.setValue("ResultFlag", "0");
		return rsBo;
	}

	public BusiObj GetIvrCountByAreaCallnum() throws BaseException {
		BusiObj rsBo = null;
		try {
			vArg.reset();
			String start_time = ((String) vInBO.getValue("start_time", "")).trim();
			String end_time = ((String) vInBO.getValue("end_time", "")).trim();
			String call_num = ((String) vInBO.getValue("call_num", "")).trim();
			String only110_flag = ((String) vInBO.getValue("only110_flag", "")).trim();
			String cond_time = " ";
			String cond_callnum = " ";
			String where = " where ";
			boolean condition = false;

			if (!(start_time == "" || end_time == "")) {
				cond_time = " and a.CALLtime between to_date(:start_time,'yyyy-mm-dd HH24:MI:SS') "
						+ " and to_date(:end_time,'yyyy-mm-dd HH24:MI:SS')";
				condition = true;
				vArg.setArg("start_time", start_time);
				vArg.setArg("end_time", end_time);
			}
			if (call_num != "") {
				cond_callnum = " calling_num = :call_num";
				/*				if (!condition)
									cond_callnum=	where	+	cond_callnum;
								else*/
				cond_callnum = " and " + cond_callnum;
				vArg.setArg("call_num", call_num);

			}
			String cond_only110=" ";
			if (only110_flag.trim().equals("1")) {
				cond_only110 = " and status in(0,4) ";//status<5 //modified at 1106
			}
/*			gSql = " select count(1) IVR_COUNT,CALLED_AREA,CALLING_NUM from "
					+ " (select nvl(called_area,'无区号')called_area,calling_num from ivr_call_record a where called_area>0 " + cond_time
					+ cond_callnum + "  )group by called_area,calling_num ";

			gSql = gSql + " union select sum(IVR_COUNT) IVR_COUNT,'小计' CALLED_AREA,'小计' CALLING_NUM from (" + gSql + ")";
			gSql = "select IVR_COUNT, CALLED_AREA, CALLING_NUM from (" + gSql + ")    order by calling_num";*/
			gSql = 
					"  select sum(valid_IVR_COUNT) valid_IVR_COUNT, " +
					" sum(invalid_IVR_COUNT) invalid_IVR_COUNT,sum(invalid_IVR_COUNT)+sum(valid_IVR_COUNT) IVR_COUNT, " +
					//" Decode(Grouping_id(CALLED_AREA,calling_num),2,'小计',3,'合计',CALLED_AREA)" +
					" CALLED_AREA," +
					//" Decode(Grouping_id(CALLED_AREA,calling_num),2,'小计',3,'所有号码',calling_num)" +
					" calling_num " +
					//"  Decode(Grouping(CALLED_AREA),1,'小计',CALLED_AREA)CALLED_AREA,calling_num " +
					" from ( " +
					" select decode(status, 0, IVR_COUNT, 0) valid_IVR_COUNT, " +
					" decode(status, 1, IVR_COUNT, 0) invalid_IVR_COUNT," +
					" calling_num,CALLED_AREA" +
					" from (select count(1) IVR_COUNT, CALLED_AREA,calling_num, status " +
					" from (select decode(trim(called_area),-1,'未明确',null,'未明确','','未明确',called_area) called_area,calling_num," +//add at 1104
					" decode(status,0,0,2,0,3,0,1) status " +
					" from ivr_call_record a " +
					" where 1=1 " +
					" and not(called_area is  null and trim(called_area) ='' ) " +//add at 20101111
					cond_only110+ 
					//" and called_area > 0 " +
					//" and status < 8 " +
					" "
					+ cond_time+ cond_callnum
					+" ) group by called_area,calling_num,status)" 
					+") " 
					+" group by " +
					//"rollup(" +
					"calling_num,called_area " 
					//+	") " 
					//+" having Grouping_id(CALLED_AREA,calling_num)<>2"
					;//add at 1104
			vLogger.info("sql:" + gSql);
			rsBo = OperAgent.execQuery(vConn, "GetIvrCountByAreaCallnum", gSql, vArg);
		} catch (Exception e) {
			e.printStackTrace();
			throw new BaseException(vName + "-010", "按照区号外呼统计失败");
		}
		vLogger.info("返回数据是:\n\n\n\n" + BOHelper.boToXML(rsBo));
		vInBO.setValue("ResultFlag", "0");
		return rsBo;
	}
	
	public BusiObj GetExpDetList() throws BaseException 
	{
		BusiObj rsBo =null;
		try
		{
			vArg.reset();
			String num = ((String) vInBO.getValue("num", "")).trim();
			gSql = "select a.num id, a.num, a.callnum, decode(a.state,'0','无效','1','有效') state,a.state_time,nvl(b.name,' ') oper_staff_name "
					+ "from exception_call_list a,staff b " + " where a.oper_staff_code = b.staff_code(+) " + " and b.state(+) = '1'";
			if (num != null && num.length() > 0) {
				gSql = gSql + " and a.num = :num ";
				vArg.setArg("num", num);
			}
			////////////////////////////////////////add at 1104
			if(this.flagQueryCount)
			{
				rsBo = this.getPagingCount(gSql, vArg, "GetExpDetList");
	/*				this.totalRows=countBo.getInt("totalRows");
				this.pageCount=countBo.getInt("pageCount");
				rsBo.setString("pageCount", String.valueOf(pageCount));
				rsBo.setString("totalRows", String.valueOf(totalRows));*/
			}
			else
			{
				gSql	+=	" order by state_time desc ";
				rsBo = this.getPagingInfo(gSql, vArg, "GetExpDetList");
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new BaseException(vName + "-013", "查询疑似详单失败");
		}
		////////////////////////////////////////add at 1104
		//BusiObj rsBo = OperAgent.execQuery(vConn, "GetExpDetList", gSql, vArg);
		rsBo.setValue("ResultFlag", "0");
		//vLogger.info("返回数据是:\n\n\n\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}
}
